package edu.gatech.TNE.wonderLab7000.person;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import edu.gatech.TNE.wonderLab7000.util.WonderLogger;

/**
 * 
 * @author Team Naming Exception 6/23/11
 *
 */
public class teacher extends person {

	private String query;
    private Connection conn;
    private ResultSet result;
    public WonderLogger logger = new WonderLogger("Mine");
    
 
    private String[][] allUsers;
    private int  i, j, userLength;
    

	public teacher(String userName, int accessLevel) {
		super(userName, accessLevel);
	}
    
	/**
	 * Create a class will write to the course table in our database and edit the respected columns
	 * with the passed information. 
	 * 
	 * @param name Name of the course
	 * @param teacher Teacher responsible for the course
	 * @param description Description of the course
	 * @return True if created, false if not created
	 * @throws SQLException
	 */
public String[][] getStudents(String courseName) throws SQLException {
		
		connect();
		query = "SELECT * FROM "+courseName;
    	PreparedStatement statement = conn.prepareStatement(query);
    	allUsers = new String[100][100];
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
		} 
		
		userLength = 0;
		while (result.next()) {
			userLength++;
		}
		
		allUsers = new String[userLength][2];
		statement = conn.prepareStatement(query);
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			logger.error(e.toString());
			
		} 
		
		i = 0;
		while (result.next()) {
			j = 0;
			allUsers[i][j] = result.getString("STUDENT");
			j++;
			allUsers[i][j] = result.getString("STUDENTID");
			j++;
			
			i++;
		}
		
        conn.close();
        statement.close();
        result.close();
		return allUsers;
		
	}
	
	public boolean createClass(String name, String teacher, String description) throws SQLException
	{
		conn = connect();
		query = "SELECT * FROM COURSES WHERE COURSENAME ='" + name + "'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			logger.error(e.toString());
		} 
		
		if (!result.next()) {
            query = "INSERT INTO COURSES (COURSENAME,TEACHER,NUMSTUDENTS,NUMLABS,DESCRIPTION) VALUES ('"+name+"', '"+teacher+"', 0,0,'" + description +"')";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
            statement.close();
            result.close();

            query = "CREATE TABLE " + name + " (STUDENT varchar(100),STUDENTID varchar(100))";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
        	
        	query = "SELECT * FROM USERS WHERE USERID ='" + getUserName() + "'";
    	    statement = conn.prepareStatement(query);
    	    try {
    	    	result = statement.executeQuery();
    		} catch (SQLException e) {
    	        
    			logger.error(e.toString());
    		} 
    			
    		result.next();
    		if (result.getString("COURSES") == null) {
    			
    			query = "UPDATE USERS SET COURSES ='" + name + "' WHERE USERID ='" + getUserName() + "'";
    		   	statement = conn.prepareStatement(query);
    		    try {
    		    	result = statement.executeQuery();
    			} catch (SQLException e) {
    		        
    		        logger.error(e.toString());
    			} 
    				
    		}
    		else {
    				
    			String courseString = result.getString("COURSES") + "," + name;
    			query = "UPDATE USERS SET COURSES ='" + courseString + "' WHERE USERID ='" + getUserName() + "'";
    		   	statement = conn.prepareStatement(query);
    		    try {
    				result = statement.executeQuery();
    			} catch (SQLException e) {
    		        

    				System.out.println(e.toString());
    				logger.error(e.toString());
    			} 
    				
    		}
	        statement.close();
	        result.close();
            conn.close();
            
			return true;
        }
		else {
            conn.close();
            statement.close();
            result.close();
            return false;
		}
	}
	
	public boolean createLab(String name, String course, int teamSize) throws SQLException
	{
		conn = connect();
		String labID = course + "_" + name;
		query = "SELECT * FROM LABS WHERE labID ='" + labID + "'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		if (!result.next()) {
            query = "INSERT INTO LABS (LABID,LABNAME,COURSE,SELECTION,TEAMSIZE) VALUES ('"+labID+"', '"+name+"', '"+course+"', 0,"+teamSize+")";
        	statement = conn.prepareStatement(query);
        	try
        	{

        	result = statement.executeQuery();
            
        	}
        	catch(Exception e)
        	{
        		System.out.println(e.getMessage());
        	}

            query = "CREATE TABLE " + labID + " (STUDENT varchar(100), TEAM varchar(100), GRADE int)";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();

                
             
            String[][] students = getStudents(course);
          
            for(int i = 0; i<students.length;i++)
            {
            	
            	
	         	query = "INSERT INTO " + labID + " (STUDENT) VALUES ('"+students[i][0]+"')";
	         	conn = connect();  
	         	statement = conn.prepareStatement(query);
	            result = statement.executeQuery();
	           
            }
            
            query = "SELECT * FROM COURSES WHERE COURSENAME ='" + course + "'";
    		statement = conn.prepareStatement(query);
    	    try {
    	    	result = statement.executeQuery();
    		} catch (SQLException e) {
    	       
    			System.out.println(e.toString());
    			logger.error(e.toString());
    		} 
    		result.next();
    		int numLabs = result.getInt("NUMLABS") + 1;
    		
    		query = "UPDATE COURSES SET NUMLABS =" + numLabs + " WHERE COURSENAME ='" + course + "'";
    	    statement = conn.prepareStatement(query);
    	    try {
    			result = statement.executeQuery();
    		} catch (SQLException e) {
    	        
    			System.out.println(e.toString());
    			logger.error(e.toString());
    		}             
            
            conn.close();
            statement.close();
            result.close();
			return true;
        }
		else {
            conn.close();
            statement.close();
            result.close();
            return false;
		}
	}
		
	
	public boolean createRandomLab(String name, String course, int teamSize) throws SQLException
	{
		conn = connect();
		String labID = course + "_" + name;
		query = "SELECT * FROM LABS WHERE labID ='" + labID + "'";
    	PreparedStatement statement = conn.prepareStatement(query);
    	
        try {
			result = statement.executeQuery();
		} catch (SQLException e) {
            
			System.out.println(e.toString());
			logger.error(e.toString());
		} 
		
		if (!result.next()) {
            query = "INSERT INTO LABS (LABID,LABNAME,COURSE,SELECTION,TEAMSIZE) VALUES ('"+labID+"', '"+name+"', '"+course+"', 1 ,"+teamSize+")";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
              
            query = "CREATE TABLE " + labID + " (STUDENT varchar(100), TEAM varchar(100), GRADE int)";
        	statement = conn.prepareStatement(query);
        	result = statement.executeQuery();
        	 
            String[][] students = getStudents(course);
            
            int curTeam = teamSize;
            curTeam = students.length/teamSize;
            for(int i = 0; i<students.length;i++)
            {
            	if(curTeam == 0)
            		curTeam = students.length/teamSize;

	         	if(students[i][0] !=null)
	         		query = "INSERT INTO " + labID + " (STUDENT,TEAM) VALUES ('"+students[i][0]+"', 'Team "+curTeam+"')";

	            statement = conn.prepareStatement(query);
	            result = statement.executeQuery();
	            
	            curTeam--;
            }
            
            query = "SELECT * FROM COURSES WHERE COURSENAME ='" + course + "'";
    		statement = conn.prepareStatement(query);
    	    try {
    	    	result = statement.executeQuery();
    		} catch (SQLException e) {
    	        
    			System.out.println(e.toString());
    			logger.error(e.toString());
    		} 
    		result.next();
    		int numLabs = result.getInt("NUMLABS") + 1;
    		
    		query = "UPDATE COURSES SET NUMLABS =" + numLabs + " WHERE COURSENAME ='" + course + "'";
    	    statement = conn.prepareStatement(query);
    	    try {
    			result = statement.executeQuery();
    		} catch (SQLException e) {

    			System.out.println(e.toString());
    			logger.error(e.toString());
    		}    
    		conn.close();
            statement.close();
            result.close();
            
			return true;
        }
		else {
            conn.close();
            statement.close();
            result.close();
            return false;
		}
		
	}
	public String[][] viewLabs() throws SQLException 
		{
			String[][] allLabs;
			connect();
			query = "SELECT * FROM LABS"; // Pull all users from the database
	    	PreparedStatement statement = conn.prepareStatement(query);
	    	
	        try {
				result = statement.executeQuery();
			} catch (SQLException e) {
	            conn.close();
	            statement.close();
	            result.close();
				System.out.println(e.toString());
			} 
			
			int userLength = 0;
			while (result.next()) {
				userLength++;
			}
			
			allLabs = new String[userLength][2];
			statement = conn.prepareStatement(query);
	    	
	        try {
				result = statement.executeQuery();
			} catch (SQLException e) {
	            conn.close();
	            statement.close();
	            result.close();
				System.out.println(e.toString());
				logger.error(e.toString());
			} 
			
			int i = 0;
			while (result.next()) 
			{
					if( !result.getString("name").equals(null))
					{
						int j = 0;
						allLabs[i][j] = result.getString("LABNAME");
						j++;
						allLabs[i][j] = result.getString("COURSE");
						j++;
					}
			}
				
		        conn.close();
		        statement.close();
		        result.close();
				return allLabs;
				
		}
	public static void main(String[] args) throws SQLException
	{
		teacher teach = new teacher("mdye",2);
		teach.createLab("testingSelfSelectAgain2","courseWithStudentsReal",2);
	}
}
